/**********************************************************************************************************************
 * SplendidCRM is a Customer Relationship Management program created by SplendidCRM Software, Inc. 
 * Copyright (C) 2005-2011 SplendidCRM Software, Inc. All rights reserved.
 * 
 * This program is free software: you can redistribute it and/or modify it under the terms of the 
 * GNU Affero General Public License as published by the Free Software Foundation, either version 3 
 * of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; 
 * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 
 * See the GNU Affero General Public License for more details.
 * 
 * You should have received a copy of the GNU Affero General Public License along with this program. 
 * If not, see <http://www.gnu.org/licenses/>. 
 * 
 * You can contact SplendidCRM Software, Inc. at email address support@splendidcrm.com. 
 * 
 * In accordance with Section 7(b) of the GNU Affero General Public License version 3, 
 * the Appropriate Legal Notices must display the following words on all interactive user interfaces: 
 * "Copyright (C) 2005-2011 SplendidCRM Software, Inc. All rights reserved."
 *********************************************************************************************************************/
using System;
using System.IO;
using System.Data;
using System.Data.Common;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Text;
using System.Xml;
using System.Web;
using System.Collections;
using System.Collections.Generic;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Diagnostics;

namespace BzwayCRM
{
	/// <summary>
	/// Summary description for Excel2007Reader.
	/// </summary>
	public class Excel2007Reader
	{
		public static DateTime FromOADate(string sValue)
		{
			DateTime dt = new DateTime(1899, 12, 30);
			dt = dt.AddDays(Int32.Parse(sValue));
			return dt;
		}

		public static string GetCellValue(SharedStringTablePart stringTablePart, Stylesheet style, Cell cell, ref string sFormat)
		{
			string sValue = String.Empty;
			if ( cell.CellValue != null )
				sValue = cell.CellValue.InnerXml;
			if ( cell.DataType != null )
			{
				if ( cell.DataType.Value == CellValues.SharedString )
					return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(sValue)].InnerText;
				else if ( cell.DataType.Value == CellValues.Date )
				{
					return FromOADate(sValue).ToString();
				}
			}
			else if ( cell.StyleIndex != null )
			{
				int nStyleIndex = (int) cell.StyleIndex.Value;
				// 05/10/2011 Paul.  Lets get the format for future reference. 
				CellFormat cellXfs = style.CellFormats.ChildElements[nStyleIndex] as CellFormat;
				if ( cellXfs.NumberFormatId != null )
				{
					// http://www.lateral8.com/articles/2010/6/11/openxml-sdk-20-formatting-excel-values.aspx
					switch ( cellXfs.NumberFormatId.Value )
					{
						case  0: sFormat = "General"                 ;  break;
						case  1: sFormat = "0"                       ;  break;
						case  2: sFormat = "0.00"                    ;  break;
						case  3: sFormat = "#,##0"                   ;  break;
						case  4: sFormat = "#,##0.00"                ;  break;
						case  9: sFormat = "0%"                      ;  break;
						case 10: sFormat = "0.00%"                   ;  break;
						case 11: sFormat = "0.00E+00"                ;  break;
						case 12: sFormat = "# ?/?"                   ;  break;
						case 13: sFormat = "# ??/??"                 ;  break;
						case 14: sFormat = "mm-dd-yy"                ;  break;
						case 15: sFormat = "d-mmm-yy"                ;  break;
						case 16: sFormat = "d-mmm"                   ;  break;
						case 17: sFormat = "mmm-yy"                  ;  break;
						case 18: sFormat = "h:mm AM/PM"              ;  break;
						case 19: sFormat = "h:mm:ss AM/PM"           ;  break;
						case 20: sFormat = "h:mm"                    ;  break;
						case 21: sFormat = "h:mm:ss"                 ;  break;
						case 22: sFormat = "m/d/yy h:mm"             ;  break;
						case 37: sFormat = "#,##0 ;(#,##0)"          ;  break;
						case 38: sFormat = "#,##0 ;[Red](#,##0)"     ;  break;
						case 39: sFormat = "#,##0.00;(#,##0.00)"     ;  break;
						case 40: sFormat = "#,##0.00;[Red](#,##0.00)";  break;
						case 45: sFormat = "mm:ss"                   ;  break;
						case 46: sFormat = "[h]:mm:ss"               ;  break;
						case 47: sFormat = "mmss.0"                  ;  break;
						case 48: sFormat = "##0.0E+0"                ;  break;
						case 49: sFormat = "@"                       ;  break;
						default:
						{
							foreach ( NumberingFormat numFmt in style.NumberingFormats.ChildElements )
							{
								if ( numFmt.NumberFormatId.Value == cellXfs.NumberFormatId.Value )
								{
									sFormat = numFmt.FormatCode.Value;
								}
							}
							break;
						}
					}
				}
			}
			return sValue;
		}

		public static string SplitDimensions(string sCellRef, ref string sColumn, ref int nRow)
		{
			int i = 0;
			while ( i < sCellRef.Length && Char.IsLetter(sCellRef[i]) )
				i++;
			sColumn = sCellRef.Substring(0, i);
			string sRow = sCellRef.Substring(i);
			Int32.TryParse(sRow, out nRow);
			return sColumn;
		}

		public static int GetCellIndex(string sID)
		{
			int nCellIndex = 0;
			if ( sID.Length == 1 )
			{
				nCellIndex = sID[0] - 'A';
			}
			else if ( sID.Length == 2 )
			{
				nCellIndex  = (sID[0] - 'A') * 26;
				nCellIndex += (sID[1] - 'A');
			}
			return nCellIndex;
		}

		public static XmlDocument ConvertSpreadsheetToXml(Stream stm, string sRecordName)
		{
			XmlDocument xmlImport = new XmlDocument();
			xmlImport.AppendChild(xmlImport.CreateProcessingInstruction("xml" , "version=\"1.0\" encoding=\"UTF-8\""));
			xmlImport.AppendChild(xmlImport.CreateElement("xml"));
			
			using ( SpreadsheetDocument doc = SpreadsheetDocument.Open(stm, false) )
			{
				WorkbookPart wb = doc.WorkbookPart;
				if ( wb.Workbook.Sheets.HasChildren )
				{
					Sheet sheet = wb.Workbook.Sheets.GetFirstChild<Sheet>();
					if ( sheet != null )
					{
						WorksheetPart part = doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart;
						if ( part != null && part.Worksheet != null )
						{
							SharedStringTablePart stringTablePart = doc.WorkbookPart.SharedStringTablePart;
							WorkbookStylesPart style = doc.WorkbookPart.WorkbookStylesPart;
							SheetData data = part.Worksheet.GetFirstChild<SheetData>();
							foreach ( Row rowExcel in data.Elements<Row>() )
							{
								XmlNode xRecord = xmlImport.CreateElement(sRecordName);
								xmlImport.DocumentElement.AppendChild(xRecord);
								
								List<Cell> cells = new List<Cell>();
								foreach ( Cell cell in rowExcel.Elements<Cell>() )
								{
									cells.Add(cell);
								}
								for ( int j = 0, nField = 0; j < cells.Count; j++, nField++ )
								{
									XmlNode xField = xmlImport.CreateElement("ImportField" + nField.ToString("000"));
									xRecord.AppendChild(xField);
									
									Cell cell = cells[j];
									string sColumn = String.Empty;
									int    nRow    = 0;
									SplitDimensions(cell.CellReference, ref sColumn, ref nRow);
									int nCellIndex = GetCellIndex(sColumn);
									// 08/22/2006 Paul.  If there are any missing cells, then add them.
									while ( (nField + 1) < nCellIndex )
									{
										nField++;
										xField = xmlImport.CreateElement("ImportField" + nField.ToString("000"));
										xRecord.AppendChild(xField);
									}
									try
									{
										string sFormat = String.Empty;
										xField.InnerText = GetCellValue(stringTablePart, style.Stylesheet, cell, ref sFormat);
									}
									catch(Exception ex)
									{
										System.Diagnostics.Trace.WriteLine(ex.Message);
									}
								}
							}
						}
					}
				}
			}
			return xmlImport;
		}
	}
}


